package Jdbc;





import java.sql.*;
import java.util.ArrayList;
import java.util.List;
import java.util.Optional;
/**
 * @author oldliu
 * @since 1.0
 */
public class StudentDao {
    //增加
    public static int add(Student s){
        String sql="insert into s_student (stuno,sname,sex,telephone,qq,wechat,gradeid,enterdate,fromcity,birthday,address,pass,idcardpic) " +
                "values (?,?,?,?,?,?,?,?,?,?,?,?,?)";
        return DBUtil.executeUpdate(sql,s.getStuno(),s.getSname(),s.getSex(),s.getTelephone(),s.getQq(),s.getWechat(),
                s.getGradeid(),s.getEnterdate(),s.getFromcity(),s.getBirthday(),s.getAddress(),s.getPass(),s.getIdcardpic());
    }
    //修改
    public int update(Student stu){
        String sql = "update from s_student set sname=? where stuno=?";
        return DBUtil.executeUpdate(sql, stu.getSname(), stu.getStuno());
    }
    //删除
    public int del(Student stu){
        String sql= "delete from s_student where stuno=?";
        return DBUtil.executeUpdate(sql,stu.getStuno());
    }
    //get学号查询
    public Student get(long stuno){
        String sql="select * from s_student where stuno=?";
        Connection con = DBUtil.getConnection();
        PreparedStatement st = null;
        ResultSet rs=null;
        try {
            st=con.prepareStatement(sql);
            st.setLong(1,stuno);
            rs=st.executeQuery();
            if(rs.next()){
                Student student=new Student();
                student.setStuno(stuno);
                student.setSname(rs.getString("sname"));
                student.setAddress(rs.getString("address"));
                student.setBirthday(rs.getDate("birthday"));
                //....
                return student;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.close(con,st,rs);
        }
        return null;
    }
    //查询全部的学生
    public List<Student> findALL(){
        List<Student> datas=new ArrayList<>();
        Connection connection = DBUtil.getConnection();
        Statement st=null;
        ResultSet rs=null;
        try {
            st=connection.createStatement();
            rs = st.executeQuery("select * from s_student");
            while (rs.next()){
                Student student=new Student();
                student.setStuno(rs.getLong("stuno"));
                student.setSname(rs.getString("sname"));
                //..java.sql.Date
                student.setBirthday(rs.getDate("birthday"));
                datas.add(student);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            DBUtil.close(rs);
            DBUtil.close(st);
            DBUtil.close(connection);
        }

        return datas;
    }
    //分页查询
    public   List<Student> find(int pageno,int pagesize){
        List<Student> list = new ArrayList<>();
        Connection con=DBUtil.getConnection();
        PreparedStatement ps=null;
        ResultSet rs=null;
        try {
            ps=con.prepareStatement( "select * from s_student order by stuno limit ?,?");
            ps.setInt(1,(pageno-1)*pagesize);
            ps.setInt(2,pagesize);
            rs=ps.executeQuery();
            while (rs.next()){
                Student stu=new Student();
                stu.setStuno(rs.getLong("stuno"));
                stu.setSname(rs.getString("sname"));
                stu.setSex(rs.getString("sex"));
                stu.setTelephone(rs.getString("telephone"));
                stu.setQq(rs.getString("qq"));
                stu.setWechat(rs.getString("wechat"));
                stu.setGradeid(rs.getLong("gradeid"));
                stu.setEnterdate(rs.getDate("enterdate"));
                stu.setFromcity(rs.getString("fromcity"));
                stu.setBirthday(rs.getDate("birthday"));
                stu.setAddress(rs.getString("address"));
                stu.setPass(rs.getString("pass"));
                stu.setIdcardpic(rs.getString("idcardpic"));
                list.add(stu);
            }
            return list;
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            DBUtil.close(con,ps,rs);
        }
        return null;
    }
}
